<?php

class Lib_XlsxParse
{
    private static $obj;

    private function __construct()
    {
    }

    /**
     * Undocumented function
     *
     * @return Lib_XlsxParse
     */
    public static function getInstance()
    {
        if (is_null(self::$obj)) {
            self::$obj = new self();
        }

        return self::$obj;
    }

    public function __clone()
    {
    }

    /**
     * 解析excel.
     * @param string $realPath
     * @param int $parseRow
     * @return array
     * @throws \PhpOffice\PhpSpreadsheet\Exception|Exception
     */
    public function execute(string $realPath, int $parseRow = 2): array
    {
        $res = [];
        $ext = strtolower(pathinfo($realPath, PATHINFO_EXTENSION));
        if (!in_array($ext, ['xlsx', 'xls'])) {
            throw new \Exception('非法文件格式,标准为.xls或.xlsx结尾，传入文件名《' . pathinfo($realPath, PATHINFO_BASENAME) . '》', 1);
        }
        $reader = null;
        if ($ext == 'xlsx') {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        } elseif ($ext == 'xls') {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
        }
        $reader->setReadEmptyCells(false);
        try {
            $spreadsheet = $reader->load($realPath);
        } catch (Exception $e) {
            throw new Exception("《" . pathinfo($realPath, PATHINFO_FILENAME) . '》 文件读取失败', 1);
        }
        $sheet = $spreadsheet->getActiveSheet();
        foreach ($sheet->getRowIterator($parseRow) as $row) {
            $tmp = [];
            foreach ($row->getCellIterator() as $cell) {
                $tmp[] = $cell->getFormattedValue();
            }
            $res[$row->getRowIndex()] = $tmp;
        }

        foreach ($res as $key => $item) {
            $c = count(array_filter($item, function ($var) {
                    return $var == "" ? false : true;
                }));
            if ($c == 0) {
                unset($res[$key]);
            }
        }
        return ($res);
    }


    public function header(int $col = 1)
    {
        $base = 65;
        $rounds = 26;
        $header = [];
        for ($i = 0; $i <= $col; $i++) {
            $cols = $base + $i;

            if ($cols < 91) {
                $header[] = chr($cols);
            } elseif ($i % $rounds == 0) {
                $h = chr(ceil($i % $rounds) + $base + ceil($i / $rounds) - 1);
                $h .= chr(ceil($i % $rounds) + $base);
                $header[] = $h;
            } else {
                // 当 列小于 90时候,$h  有2部分组成 AA AB AC
                $h = chr(ceil($i / $rounds) - 2 + $base);
                $h .= chr(ceil($i % $rounds) + $base);
                $header[] = $h;
            }
        }
        return $header;
    }
}
